/*
  FILENAME	: write_input.sql
  Copyright (C) 2013  Testmonger
  See https://maketestdata.codeplex.com/license

  NOTES		: 
    - param 1 number of iterations
	- always keep record 1 as default in the case running this proc directly.
	- control record or any other rules must be run before this proc to prepare file temp table.
	- the select statement must match FileTemp columns.

  	select * from FileDefinition
    select * from FileTemp
	exec write_file persontemp
*/


IF OBJECT_ID(N'write_file') IS NOT NULL 
BEGIN
	DROP procedure write_file
END
GO

CREATE PROCEDURE write_file
	@readtable nvarchar(20)
AS
BEGIN
	DECLARE @outfile varchar(100), @vsql nvarchar(1000) , @vfileid bigint

	-- exec write_file persontemp
--	select @outfile = rtrim(@inroadhome)+rtrim(OutFileName)+'.'+rtrim(OutFileExt)
	select @outfile = rtrim(OutFilePath)+rtrim(OutFileName)+'.'+rtrim(OutFileExt), 
		@vfileid=fileid
	from FileDefinition where rtrim(filetable) like ''+@readtable+''

	--debug
	select @outfile,@readtable, @vfileid

	select @vsql = 'bcp "'+dbo.ufnTableQryRTRIM(@vfileid)+'" queryout "'+rtrim(@outfile)+'" -c -T -t, -S '+@@SERVERNAME+' -d '+DB_NAME()

	select @vsql 
	-- 	exec write_file persontemp

	BEGIN
		EXEC master..xp_cmdshell @vsql , no_output 
	END

END

-- bcp InRoad.dbo.FileTemp out E:\APPS\InRoad\OutTarget\DefaultInput.txt -c -T -t, -S HP-PC\SQLEXPRESS 


/*
SET @bcpCommand = 'bcp "SELECT rtrim(keyword),rtrim(bank),rtrim(lob),rtrim(dg),rtrim(date),rtrim(maid),rtrim(txncount),'
SET @bcpCommand = @bcpCommand+' rtrim(txnamount),rtrim(categorical) FROM '+DB_NAME()+'.dbo.FileTemp '
SET @bcpCommand = @bcpCommand+' ORDER BY SEQUENCE,keyword" queryout "'+@opfilename+'" -c -T -t,'
*/
